package com.iris.live.services.data.repositories;

import com.iris.live.services.data.models.Lsh导入名单Model;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

/**
 * Created by sxy on 2016/7/13
 */
@Transactional
public interface Lsh导入名单Repository extends JpaRepository<Lsh导入名单Model, Integer>, JpaSpecificationExecutor<Lsh导入名单Model> {
    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.许可=?2 order by a.更新时间 desc")
    List<Lsh导入名单Model> findLatestRecord(String phone, String permit);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.许可=?2 and a.是否有效线索='是' and (a.线索创建日期>=?3 and a.线索创建日期<=?4) order by a.线索创建日期 desc ")
    List<Lsh导入名单Model> isAvailableClue(String phone, String permit, Date startTime, Date endDate);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.活动编号=?3 and a.线索创建日期=?2 and a.许可=?4")
    List<Lsh导入名单Model> findByPhoneAndDateAndActivityCode( String phone,Date nowDate,Integer code,String permit);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.活动编号=?3 and (a.线索创建日期>=?2) and a.许可=?4")
    List<Lsh导入名单Model> findByPhoneAndDateAndActivityCodeAndPermit(String phone, Date nowDate, Integer code, String permit);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.许可=?2 and a.是否有效线索='是' order by a.线索创建日期 desc")
    List<Lsh导入名单Model> findByPhoneAndPermit(String phone,String permit);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.许可=?2  order by a.线索创建日期 desc")
    List<Lsh导入名单Model> findByPhoneAndPermitDesc(String phone,String permit);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.许可=?2 and a.线索创建日期>=?3 order by a.线索创建日期 desc")
    List<Lsh导入名单Model> findByPhoneAndPermitAndTimeDesc(String phone, String permit, Date ts);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?2 and a.许可=?3 and a.活动编号=?1 and a.导入日期<?4")
    List<Lsh导入名单Model> findBy活动编号And电话And许可And导入日期(Integer 活动编号,String 电话号码,String 许可,Timestamp 导入日期);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.许可=?2 and a.是否有效线索='是'and a.线索创建日期>=?3 order by a.线索创建日期 desc")
    List<Lsh导入名单Model> findByPhoneAndPermitAndTimeDesc1(String phone, String permit, Date ts);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.活动编号=?2 and (a.线索创建日期>?4) and a.许可=?3")
    List<Lsh导入名单Model> findByPhoneAndEndDateAndActivityCodeAndPermit(String phone, Integer code, String permit, Date date);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.活动编号=?2 and (a.线索创建日期<?4) and a.许可=?3")
    List<Lsh导入名单Model> findByPhoneAndStartDateAndActivityCodeAndPermit(String phone, Integer code, String permit, Date date);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.活动编号=?2  and a.许可=?3")
    List<Lsh导入名单Model> findByPhoneAndActivityCode(String phone, Integer code, String permit);

    @Query("select a from Lsh导入名单Model a where a.电话号码=?1 and a.活动编号=?2  and a.许可=?3 and (a.导入状态=0 or a.导入状态=1)")
    List<Lsh导入名单Model> findByPhoneAndActivityCodeAndState(String phone, Integer code, String permit);

    @Query("select a from Lsh导入名单Model a where   a.战败去向 IS NOT NULL AND a.战败去向 <> '' AND a.顾客编号 =?1  ")
    List<Lsh导入名单Model> findByclientId(int phone);

    // 根据许可取出数据
    @Query("select a from Lsh导入名单Model a where a.许可 = ?1")
    List<Lsh导入名单Model> getImportMoListByPermit(String permit);

    //更新销售顾问列
    @Modifying
    @Query("update Lsh导入名单Model a set a.所属销售顾问=?4 where a.电话号码=?1 and a.活动编号=?2 and a.许可 = ?3")
    void updSc(String phone,Integer code,String permit,String sc);

}
